import Main.DbToExcel;
import com.mongodb.MongoClient;
import com.mongodb.QueryBuilder;
import com.mongodb.client.FindIterable;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.bson.BSON;
import org.bson.BSONObject;
import org.bson.BasicBSONObject;
import org.bson.Document;
import org.bson.conversions.Bson;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.management.Query;
import javax.print.CancelablePrintJob;
import javax.print.Doc;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

public class test {
    public static void main(String[] args) throws IOException, InvalidFormatException {
        Map<String, Set<String>> map = getData();
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        int index = 0;
        for (String key : map.keySet()){
            Set<String> set = map.get(key);
            Row row = sheet.createRow(index++);
            StringBuilder sb = new StringBuilder();
            for (String val : set){
                sb.append(val+"、");
            }
            String str = sb.toString();
            row.createCell(0).setCellValue(key);
            row.createCell(1).setCellValue(str.substring(0, sb.length()-1));
        }
        workbook.write(new FileOutputStream("D:\\测试临时文件\\华中科技大学\\华中科技大学管理学院2020.09.14-2021.07.07.xlsx"));
    }

    public static Map<String, Set<String>> getData() throws IOException, InvalidFormatException {
        String filePath = "D:\\测试临时文件\\华中科技大学管理学院2020.09.14-2021.07.07.xlsx";
        Workbook workbook = WorkbookFactory.create(new File(filePath));
        Sheet sheet = workbook.getSheetAt(1);
        final int RowSize = sheet.getPhysicalNumberOfRows();
        Map<String, Set<String>> map = new HashMap<>();
        for (int i = 1; i < RowSize ; i++) {
            Row row = sheet.getRow(i);
            String key = row.getCell(1)+"";
            String val = row.getCell(0)+"";
            if (map.containsKey(key)){
                map.get(key).add(val);
            }else {
                Set<String> set = new HashSet();
                set.add(val);
                map.put(key, set);
            }
        }
        return map;
    }
}
